import json
import pymysql
from flask import Flask, request
from flask_cors import CORS

app = Flask(__name__)
CORS(app, supports_credentials=True)
DATABASE = "softwaretesting"



@app.route('/hello')
def hello_world():  # put application's code here
    return 'Hello World!'


@app.route('/add', methods=['get', 'post'])
def add():
    if request.method == 'GET':
        id = request.values.get('id')
        name = request.values.get('name')
        age = request.values.get('age')

    elif request.method == 'POST':
        try:
            id = request.form['id']
            name = request.form['name']
            age = request.form['age']
        except KeyError:
            ret = {'code': 10001, 'message': '参数不能为空！'}
            return json.dumps(ret, ensure_ascii=False)
    if id and name and age:
        # 打开数据库连接
        db = pymysql.connect(host="localhost", user="root", password="123456", database=DATABASE)
        # 使用cursor（）方法创建一个游标对象cursor
        cursor = db.cursor()
        # sql插入语句 拼接注意字符串要加引号
        sql = "insert into users (id,name,age) values ('" + id + "','" + name + "'," + age + ")"
        print(sql)
        try:
            cursor.execute(sql)
            # 提交
            db.commit()
            ret = {'code': 200, 'message': '添加成功'}
            return json.dumps(ret)
        except Exception as e:
            # 错误回滚
            db.rollback()
            ret = {'code': 10005, 'message': '添加失败'}
            return json.dumps(ret)
        finally:
            db.close()
    ret = {'code': 10003, 'message': '存在为空的参数'}
    return json.dumps(ret)


@app.route('/delete', methods=['get', 'post'])
def delete():
    if request.method == 'GET':
        id = request.values.get('id')
    elif request.method == 'POST':
        try:
            id = request.form['id']
        except KeyError:
            ret = {'code': 10001, 'message': '参数不能为空！'}
            return json.dumps(ret, ensure_ascii=False)
    if id:
        # 打开数据库连接
        db = pymysql.connect(host="localhost", user="root", password="123456", database=DATABASE)
        # 使用cursor（）方法创建一个游标对象cursor
        cursor = db.cursor()
        # sql删除语句
        sql = "delete from users where id =" + id
        try:
            l = cursor.execute(sql)
            print(l)
            # 提交
            db.commit()
            if(l == 0):
                ret = {'code': 100010, 'message': '不存在该用户'}
            else:
                ret = {'code': 200, 'message': '删除成功'}
            return json.dumps(ret)
        except Exception as e:
            # 错误回滚
            db.rollback()
            ret = {'code': 10004, 'message': '删除失败'}
            return json.dumps(ret)
        finally:
            db.close()
    ret = {'code': 10003, 'message': '存在为空的参数'}
    return json.dumps(ret)


@app.route('/modify', methods=['get', 'post'])
def modify():
    if request.method == 'GET':
        id = request.values.get('id')
        name = request.values.get('name')
        age = request.values.get('age')
    elif request.method == 'POST':
        try:
            id = request.form['id']
            name = request.form['name']
            age = request.form['age']
        except KeyError:
            ret = {'code': 10001, 'message': '参数不能为空！'}
            return json.dumps(ret, ensure_ascii=False)
    if id and name and age:
        # 打开数据库连接
        db = pymysql.connect(host="localhost", user="root", password="123456", database=DATABASE)
        # 使用cursor（）方法创建一个游标对象cursor
        cursor = db.cursor()
        # sql修改语句
        sql = "update users set age =" + age + ", name = '" + name + "'where id = " + id
        try:
            l = cursor.execute(sql)
            # 提交
            db.commit()
            if (l == 0):
                ret = {'code': 100010, 'message': '不存在该用户'}
            else:
                ret = {'code': 200, 'message': '修改成功'}
            return json.dumps(ret)
        except Exception as e:
            # 错误回滚
            db.rollback()
            ret = {'code': 10002, 'message': '修改失败'}
            return json.dumps(ret)
        finally:
            db.close()
    ret = {'code': 10003, 'message': '存在为空的参数'}
    return json.dumps(ret)


@app.route('/selectOne', methods=['get', 'post'])
def selectOne():
    if request.method == 'GET':
        id = request.values.get('id')
    elif request.method == 'POST':
        try:
            id = request.form['id']
        except KeyError:
            ret = {'code': 10001, 'message': '参数不能为空！'}
            return json.dumps(ret, ensure_ascii=False)
    if id:
        # 打开数据库连接
        db = pymysql.connect(host="localhost", user="root", password="123456", database=DATABASE)
        # 使用cursor（）方法创建一个游标对象cursor
        cursor = db.cursor()
        # sql查询语句
        sql = "select * from users where id = " + id
        try:
            cursor.execute(sql)
            # 提交
            db.commit()
            results = cursor.fetchall()
            # 遍历结果
            for rows in results:
                id = rows[0]
                name = rows[1]
                age = rows[2]
            print("id=%s,name=%s,age=%s" % (id, name, age))
            res = {'id': id, 'name': name, 'age': age}
            data = {'code': 200, 'data': res}
            return json.dumps(data)
        except Exception as e:
            # 错误回滚
            db.rollback()
            ret = {'code': 10006, 'message': '查询失败'}
            return json.dumps(ret)
        finally:
            db.close()
    ret = {'code': 10003, 'message': '存在为空的参数'}
    return json.dumps(ret)


@app.route('/selectAll', methods=['get', 'post'])
def selectAll():
    # 打开数据库连接
    db = pymysql.connect(host="localhost", user="root", password="123456", database=DATABASE)
    # 使用cursor（）方法创建一个游标对象cursor
    cursor = db.cursor()
    # sql查询语句
    sql = "select * from users "
    res = []
    try:
        cursor.execute(sql)
        # 提交
        db.commit()
        results = cursor.fetchall()
        # 遍历结果
        for rows in results:
            id = rows[0]
            name = rows[1]
            age = rows[2]
            temp = {'id': id, 'name': name, 'age': age}
            res.append(temp)
            print("id=%s,name=%s,age=%s" % (id, name, age))
        data = {'code': 200, 'data': res}
        return json.dumps(data)
    except Exception as e:
        # 错误回滚
        db.rollback()
        ret = {'code': 10006, 'message': '查询失败'}
        return json.dumps(ret)
    finally:
        db.close()


if __name__ == '__main__':
    app.run(host='0.0.0.0')
